﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace University.Workshop.DataAccess
{
    public static class RoleDataAccess
    {
        public static DataTable VerifyPermission(string ciUser, string nameComponent)
        {
            //Buscar un manager con ci=ciUser que tenga una relacion con Function con "nameComponent"
            string stringConnection = "select * from Function f, Manager m, Role r, RoleFunction fr, ManagerRole ru  where ru.Manager_CiManager=m.CiManager and ru.Role_IdRole=r.IdRole and r.IdRole=fr.Role_IdRole and fr.Function_IdFunction=f.IdFunction and m.CiManager='" + ciUser + "' and f.NameFunction='" + nameComponent + "'";
            using (DBConnection connection = new DBConnection())
            {
                return connection.ConsultationSelectDataTable(stringConnection);
            }
        }


        private static bool ExecuteConsult(string stringConnection)
        {
            using (DBConnection connection = new DBConnection())
            {
                if (connection.CommandExecuteConsult(stringConnection))
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }


        public static DataTable LoadDataFunctions(string nameRole)
        {
            string stringConnection = "select NameFunction from Function where NameFunction not in (select f.NameFunction from Function f, RoleFunction fr, Role r where f.IdFunction=fr.Function_IdFucntion and fr.Role_IdRole=r.IdRole and r.NameRole='" + nameRole + "')";
            using (DBConnection connection = new DBConnection())
            {
                return connection.ConsultationSelectDataTable(stringConnection);
            }
        }

        public static DataTable LoadDataRoles()
        {
            string stringConnection = "select NameRole from Role";
            using (DBConnection connection = new DBConnection())
            {
                return connection.ConsultationSelectDataTable(stringConnection);
            }
        }

        
        public static DataTable LoadDataFunctionOfRole(string nameRole)
        {
            string stringConnection = "select f.NameFunction from Function f, RoleFunction fr where f.IdFunction=fr.Function_IdFucntion and fr.Role_IdRole'" + nameRole + "'";
            using (DBConnection connection = new DBConnection())
            {
                return connection.ConsultationSelectDataTable(stringConnection);
            }
        }

        public static bool AddFunction(string nameRole, string nameFunction)
        {
            string stringConnection = "insert into RoleFunction (NameRole,NameFunction) values('" + nameRole + "','" + nameFunction + "')";
            return ExecuteConsult(stringConnection);
        }

        public static bool RemoveFunctionOfTheRole(string nameRole, string nameFunction)
        {
            string stringConnection = "Delete from RoleFunction where NameRole='" + nameRole + "' and NameFunction='" + nameFunction + "'";
            return ExecuteConsult(stringConnection);
        }

        public static bool AddRole(string nameRole)
        {
            string stringConnection = "insert into Role (NameRole) values('" + nameRole+ "')";
            return ExecuteConsult(stringConnection);
        }

        public static DataTable LoadDataRoles(string nameRole)
        {
            string stringConnection = "select * from Role where NameRole='" + nameRole + "'";
            using (DBConnection connection = new DBConnection())
            {
                return connection.ConsultationSelectDataTable(stringConnection);
            }
        }

        public static bool DeleteRole(string nameRole)
        {
            string stringConnection = "Delete from FunctionRole where NameRole='" + nameRole + "'";
            string stringConnection2 = "Delete from RoleUser where NameRole='" + nameRole + "'";
            string stringConnection3 = "Delete from Roles where NameRole='" + nameRole + "'";
            ExecuteConsult(stringConnection);
            ExecuteConsult(stringConnection2);
            return ExecuteConsult(stringConnection3);  
        }

        public static DataTable LoadDataRolesOfTheUser(string ciUser)
        {
            string stringConnection = "select r.NameRole from Manager m, Roles r, RoleUser ru  where r.NameRole=ru.NameRole and ru.CiUser=m.Ci and m.Ci='" + ciUser + "'";
            using (DBConnection connection = new DBConnection())
            {
                return connection.ConsultationSelectDataTable(stringConnection);
            }
        }

        public static DataTable LoadRolesAvailableForTheUserSelected(string ciUser)
        {
            string stringConnection = "select NameRole from Roles where NameRole not in (select ru.NameRole from RoleUser ru, Manager m where ru.CiUser=m.Ci and m.Ci='" + ciUser + "')";
            using (DBConnection connection = new DBConnection())
            {
                return connection.ConsultationSelectDataTable(stringConnection);
            }
        }

        public static bool AddRoleToUser(string ciUser, string nameRole)
        {
            string stringConnection = "insert into RoleUser (CiUser, NameRole) values('" + ciUser + "','" + nameRole + "')";
            return ExecuteConsult(stringConnection);
        }

        public static bool RemoveRoleOfTheUser(string ciUser, string nameRole)
        {
            string stringConnection = "Delete from RoleUser where CiUser='" + ciUser + "' and NameRole='" + nameRole + "'";
            return ExecuteConsult(stringConnection);
        }
    }
}

